﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DoeObjects
{
    public class SlimLocationCategory
    {

        public string Name { get; set; }
        public int CategoryId { get; set; }
        public int Count { get; set; }


        public static List<SlimLocationCategory> getAllFromUser(int userId)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand("SELECT LoanableCategoryName, LoanableCategory.LoanableCategoryId, COUNT(*) AS NrOfLoanables FROM LoanableCategory INNER JOIN [User] ON [User].UserId = @UserID INNER JOIN Loanable ON Loanable.LoanableCategoryId = LoanableCategory.LoanableCategoryId GROUP BY LoanableCategory.LoanableCategoryId, LoanableCategoryName;", connection);
            command.Parameters.AddWithValue("@UserID", userId);

            List<SlimLocationCategory> cats = new List<SlimLocationCategory>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    SlimLocationCategory newCat = new SlimLocationCategory();
                    newCat.CategoryId = (int)reader["LoanableCategoryId"];
                    newCat.Name = reader["LoanableCategoryName"] as string;
                    newCat.Count = (int)reader["NrOfLoanables"];
                    cats.Add(newCat);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return cats;
        }


        

    }
}
